package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import pojo.Food;
import pojo.FoodType;
import util.DBUtil;
import util.DBUtil.ConnType;
import dao.FoodDao;

public class FoodDaoImpl implements FoodDao{

	@Override
	public List<Food> findAllFoods(String mid, int start, int end) {
		List<Food> list = new ArrayList<Food>();
		Connection con = null;
  		PreparedStatement pst = null;
  		ResultSet rs = null;
  	
        //String sql1 = "";
  		String sql = "select * from ((select * from food where m_id=?) rs1 left join food_type rs2 on rs1.type_id = rs2.id)";
  		//String sql ="select * from food join food_type on food.type_id = food_type.id where food.m_id=?";
  		Food f = null;
  
  		try {
  			con = DBUtil.createConnection(ConnType.JNDI_ORACLE);
			pst = con.prepareStatement(sql);
			pst.setString(1, mid);
			rs = pst.executeQuery();
			
			while(rs.next()){
				f = new Food();
				f.setId(rs.getString("id"));
				f.setName(rs.getString("name"));
				f.setPrice(rs.getDouble("price"));
				f.setImg(rs.getString("img"));
				f.setType(rs.getString("type_id"));
				f.setTypeName(rs.getString("type"));
				f.setMid(mid);
				list.add(f);
			}
		} catch (Exception e) {
	 
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		} 
  		
  		return list;
	}

	@Override
	public int addFood(Food f) {
		Connection con = null;
  		PreparedStatement pst = null;
  
  		String sql = "insert into food(id,name,price,img,type_id,m_id) values(?,?,?,?,?,?)";
  		FoodType t = null;
        int m = 0;
  		try {
  			con = DBUtil.createConnection(ConnType.JNDI_ORACLE);
			pst = con.prepareStatement(sql);
			pst.setString(1, f.getId());
			pst.setString(2, f.getName());
			pst.setDouble(3, f.getPrice());
			pst.setString(4, f.getImg());
			pst.setString(5, f.getType());
			pst.setString(6, f.getMid());
			m = pst.executeUpdate();
			
	 
		} catch (Exception e) {
	 
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		} 
  		
  		return m;
	}

	@Override
	public List<Food> findAllFoodsByType(String type, int start, int end) {
		List<Food> list = new ArrayList<Food>();
		Connection con = null;
  		PreparedStatement pst = null;
  		ResultSet rs = null;
  
  		String sql = "select * from ((select * from food where type_id=?) rs1 left join food_type rs2 on rs1.type_id = rs2.id)";
  		//String sql ="select * from food join food_type on food.type_id = food_type.id where food.m_id=?";
  		Food f = null;
  
  		try {
  			con = DBUtil.createConnection(ConnType.JNDI_ORACLE);
			pst = con.prepareStatement(sql);
			pst.setString(1, type);
			rs = pst.executeQuery();
			
			while(rs.next()){
				f = new Food();
				f.setId(rs.getString("id"));
				f.setName(rs.getString("name"));
				f.setPrice(rs.getDouble("price"));
				f.setImg(rs.getString("img"));
				f.setType(rs.getString("type_id"));
				f.setTypeName(rs.getString("type"));
				f.setMid(rs.getString("m_id"));
				
			
				list.add(f);
			}
		} catch (Exception e) {
	 
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		} 
  		
  		return list;
	}

	@Override
	public List<Food> findAllFoods(String mid, String type, int start, int end) {
		List<Food> list = new ArrayList<Food>();
		Connection con = null;
  		PreparedStatement pst = null;
  		ResultSet rs = null;
  
  		String sql = "select * from ((select * from food where m_id=? and type_id=? ) rs1 left join food_type rs2 on rs1.type_id = rs2.id)";

  		Food f = null;
  
  		try {
  			con = DBUtil.createConnection(ConnType.JNDI_ORACLE);
			pst = con.prepareStatement(sql);
			pst.setString(1, mid);
			pst.setString(2, type);
			rs = pst.executeQuery();
			
			while(rs.next()){
				f = new Food();
				f.setId(rs.getString("id"));
				f.setName(rs.getString("name"));
				f.setPrice(rs.getDouble("price"));
				f.setImg(rs.getString("img"));
				f.setType(rs.getString("type_id"));
				f.setTypeName(rs.getString("type"));
				f.setMid(rs.getString("m_id"));
				
				//f.setImg(rs.getString("img"));
				list.add(f);
			}
		} catch (Exception e) {
	 
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		} 
  		
  		return list;
	}

	@Override
	public List<String> findAllMerchantIdByFoodTypeId(String tid) {
		List<String> list = new ArrayList<String>();
		Connection con = null;
  		PreparedStatement pst = null;
  		ResultSet rs = null;
  
  		String sql = "select m_id from food where type_id=?";

  
  		try {
  			con = DBUtil.createConnection(ConnType.JNDI_ORACLE);
			pst = con.prepareStatement(sql);
			pst.setString(1, tid);
			rs = pst.executeQuery();
			
			while(rs.next()){
				list.add(rs.getString("m_id"));
			}
		} catch (Exception e) {
	 
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		} 
  		
  		return list;
	}

	@Override
	public Food searchFood(String fid) {
		Connection con = null;
  		PreparedStatement pst = null;
  		ResultSet rs = null;
  
  		String sql = "select * from ((select * from food where id=?) rs1 left join food_type rs2 on rs1.type_id = rs2.id)";
  		//String sql ="select * from food join food_type on food.type_id = food_type.id where food.m_id=?";
  		Food f = null;
  
  		try {
  			con = DBUtil.createConnection(ConnType.JNDI_ORACLE);
			pst = con.prepareStatement(sql);
			pst.setString(1, fid);
			rs = pst.executeQuery();
			
			if(rs.next()){
				f = new Food();
				f.setId(rs.getString("id"));
				f.setName(rs.getString("name"));
				f.setPrice(rs.getDouble("price"));
				f.setImg(rs.getString("img"));
				f.setType(rs.getString("type_id"));
				f.setTypeName(rs.getString("type"));
				f.setMid(rs.getString("m_id"));
				
				return f;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		} 
  		
  		return null;
	}

}
